package com.yzyz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.yzyz.bean.scenic.PictureBean;
import com.yzyz.dao.PictureDao;
import com.yzyz.utils.mapper.PictureRowMapper;

/**
 * 
 * @author David Sun
 * @Date   2010-3-21
 *
 */
public class PictureDaoImpl implements PictureDao {
	private JdbcTemplate jdbcTemplate;
	@Override
	public PictureBean get(String name) {
		
		return null;
	}

	@Override
	public int delete(int id) {
		String sql = "delete from picture where id=" + id;
		return this.jdbcTemplate.update(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Object get(int id) {
		String sql = "select * from picture where id=" + id;
		List<PictureBean> list = this.jdbcTemplate.query(sql, new PictureRowMapper());
		if(list == null) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public int save(Object object) {
		final PictureBean p = (PictureBean)object;
		final String sql = "insert into picture (contents,ownerId,date,description,name) values(?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
			public java.sql.PreparedStatement createPreparedStatement(
					Connection conn) throws SQLException {
				java.sql.PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setBytes(1, p.getContents());
				ps.setInt(2, p.getOwnerId());
				ps.setTimestamp(3, new java.sql.Timestamp(p.getDate().getTime()));
				ps.setString(4, p.getDescription());
				ps.setString(5, p.getName());
				return ps;
			}
		}, keyHolder);
		p.setId(keyHolder.getKey().intValue());
		return keyHolder.getKey().intValue();

	}

	@Override
	public void update(Object object) {
		final PictureBean p = (PictureBean) object;
		String sql = "update picture set name=?, description=? where id=?";
		this.jdbcTemplate.update(sql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, p.getName());
				ps.setString(2, p.getDescription());
				ps.setInt(3, p.getId());
				
			}});

	}

	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		
	}

	@Override
	public int updateAvailable(int id, boolean available) {
		String sql = "update picture set available=? where id=?";
		return this.jdbcTemplate.update(sql, new Object[]{available,id});
	}

	@Override
	public int getCount(int ownerId) {
		String sql = "select count(*) from picture where ownerId=" + ownerId; 
		return this.jdbcTemplate.queryForInt(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PictureBean> getPicturesWithoutContents(int scenicId) {
		String sql = "select id, ownerId, description, name, available from picture where ownerId=" + scenicId;
		
		return this.jdbcTemplate.query(sql, new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				PictureBean p = new PictureBean();
				p.setAvailable(rs.getBoolean("available"));
				p.setDescription(rs.getString("description"));
				p.setId(rs.getInt("id"));
				p.setName(rs.getString("name"));
				p.setOwnerId(rs.getInt("ownerId"));
				return p;
			}});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PictureBean> getPicture(int pictureId) {
		String sql = "select * from picture where id=" + pictureId;
		return this.jdbcTemplate.query(sql, new PictureRowMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public PictureBean getPictureByAttName(String name, int scenicId) {
		String sql = "select * from picture where ownerId=" + scenicId + " and name=" + name;
		List<PictureBean> ps = this.jdbcTemplate.query(sql, new PictureRowMapper());
		if(ps == null || ps.size() == 0){
			return null;
		}
		return ps.get(0);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PictureBean> getPicturesByScenic(int scenicId) {
		String sql = "select * from picture where ownerId=" + scenicId;
		return this.jdbcTemplate.query(sql, new PictureRowMapper());
	}

}
